package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IReportFormDao;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class ReportFormDaoImpl extends BaseDaoImpl implements IReportFormDao {

    @Override
    public List<Map<String, Object>> salarySign() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from vm_sal_bank_salary_info");
        return super.getjdbcTemplate().queryForList(sb.toString());
    }

    @Override
    public String selectCurrentSalaryMonth() {
        StringBuilder sb = new StringBuilder();
        sb.append(" select DISTINCT salary_month from ")
                .append(TableName.AGENCY_VERIFY)
                .append(" where is_deleted = 0 and is_month_end = 0 ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), String.class);
    }

    @Override
    public List<Map<String, Object>> queryUnitSalarySummaryPage(PaginationDTO paginationDTO, String salaryMonth, String salaCardBank) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select * from vw_sal_agency_salary_collect where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salaryMonth = ? ");
            paramList.add(salaryMonth);
        }
        if(!StringUtils.isEmpty(salaCardBank)){
            sb.append(" and salaCardBank = ? ");
            paramList.add(salaCardBank);
        }
        sb.append(" order by salaryMonth ");
        String sql = super.boxUpSql(sb.toString());
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(sql, params);
    }

    @Override
    public int queryUnitSalarySummaryTotal(String salaryMonth, String salaCardBank) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select count(*) from vw_sal_agency_salary_collect where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salaryMonth = ? ");
            paramList.add(salaryMonth);
        }
        if(!StringUtils.isEmpty(salaCardBank)){
            sb.append(" and salaCardBank = ? ");
            paramList.add(salaCardBank);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public Map<String,Object> queryUnitSalarySummarySum(String salaryMonth, String salaCardBank){
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" ifNull(sum(应发合计),0) 应发合计, ")
                .append(" ifNull(sum(应发工资),0) 应发工资, ")
                .append(" ifNull(sum(个人所得税),0) 个人所得税, ")
                .append(" ifNull(sum(住房公积金),0) 住房公积金, ")
                .append(" ifNull(sum(医疗保险金),0) 医疗保险金, ")
                .append(" ifNull(sum(养老保险),0) 养老保险, ")
                .append(" ifNull(sum(职业年金),0) 职业年金, ")
                .append(" ifNull(sum(补扣工资),0) 补扣工资, ")
                .append(" ifNull(sum(扣款合计),0) 扣款合计, ")
                .append(" ifNull(sum(实发工资),0) 实发工资, ")
                .append(" ifNull(sum(应发离休费),0) 应发离休费, ")
                .append(" ifNull(sum(代扣款),0) 代扣款, ")
                .append(" ifNull(sum(实发离休费),0) 实发离休费, ")
                .append(" ifNull(sum(小计),0) 小计, ")
                .append(" ifNull(sum(在职),0) 在职, ")
                .append(" ifNull(sum(离休),0) 离休 ")
                .append(" from vw_sal_agency_salary_collect where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salaryMonth = ? ");
            paramList.add(salaryMonth);
        }
        if(!StringUtils.isEmpty(salaCardBank)){
            sb.append(" and salaCardBank = ? ");
            paramList.add(salaCardBank);
        }
        return super.getjdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> queryUnitBankRetiredPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from vw_sal_agency_retired_person where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        sql.append(" order by agency_code");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int queryUnitBankRetiredTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from vw_sal_agency_retired_person where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    @Override
    public Map<String, Object> queryUnitBankRetiredSum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append("sum(人数) AS `人数`,sum(应发项) AS `应发项`,sum(实发工资) AS `实发工资`,sum(`职务(职级)工资`) AS `职务(职级)工资`,sum(级别工资) AS `级别工资`,")
                .append("sum(岗位工资) AS `岗位工资`,sum(等级工资) AS `等级工资`,sum(行业津贴) AS `行业津贴`,sum(独生子女费) AS `独生子女费`,sum(住房补贴) AS `住房补贴`,")
                .append("sum(倒级差) AS `倒级差`,sum(电话费) AS `电话费`,sum(电话补贴) AS `电话补贴`,sum(奖励保留) AS `奖励保留`,sum(见习费) AS `见习费`,")
                .append("sum(工作性津贴) AS `工作性津贴`,sum(生活性补贴) AS `生活性补贴`,sum(岗位津贴) AS `岗位津贴`,sum(交通补贴) AS `交通补贴`,")
                .append("sum(工人奖金) AS `工人奖金`,sum(节日补贴) AS `节日补贴`,sum(其它一) AS `其它一`,sum(其它二) AS `其它二`,sum(其它三) AS `其它三`,")
                .append("sum(补发工资) AS `补发工资`,sum(代扣款) AS `代扣款`,sum(补扣工资) AS `补扣工资`,sum(所得税) AS `所得税`,sum(住房公积金) AS `住房公积金`,")
                .append("sum(医疗保险) AS `医疗保险`,sum(养老保险) AS `养老保险`,sum(职业年金) AS `职业年金`,sum(失业保险) AS `失业保险`,sum(扣发项) AS `扣发项`,")
                .append("sum(标准工资) AS `标准工资`,sum(警衔津贴) AS `警衔津贴`,sum(地区补贴) AS `地区补贴`,sum(薪级工资) AS `薪级工资`,sum(绩效奖金) AS `绩效奖金`,")
                .append("sum(津贴) AS `津贴`,sum(提高部分) AS `提高部分`,sum(生活补贴) AS `生活补贴`,sum(剩余津贴补贴) AS `剩余津贴补贴`,sum(正常增离退休费) AS `正常增离退休费`,")
                .append("sum(午餐费) AS `午餐费`,sum(书报费) AS `书报费`,sum(洗理费) AS `洗理费`,sum(护理费) AS `护理费`,sum(工龄工资) AS `工龄工资`,sum(基础工资) AS `基础工资`,")
                .append("sum(浮动工资) AS `浮动工资`,sum(交通费) AS `交通费`,sum(十三月工资) AS `十三月工资`");
        sql.append(" from vw_sal_agency_retired_person where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        sql.append(" order by agency_code");
        return this.getJdbcTemplate().queryForMap(sql.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> queryUnitBankWorkPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from vw_sal_agency_no_retired_person where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        sql.append(" order by agency_code");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int queryUnitBankWorkTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from vw_sal_agency_no_retired_person where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

    @Override
    public Map<String, Object> queryUnitBankWorkSum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select sum(人数) 人数, sum(应发项) 应发项, sum(扣发项) 扣发项, sum(实发工资) 实发工资, sum(补扣工资) 补扣工资, ")
                .append(" sum(个人所得税) 个人所得税, sum(住房公积金) 住房公积金, sum(医疗保险) 医疗保险, sum(养老保险) 养老保险, sum(职业年金) 职业年金 ")
                .append(" from vw_sal_agency_no_retired_person where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        return this.getJdbcTemplate().queryForMap(sql.toString(), paramList.toArray());
    }
    @Override
    public List<Map<String, Object>> vwSalPersonChangeAdd(PaginationDTO paginationDTO, String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select * from vw_sal_person_change_add where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salary_month = ? ");
            paramList.add(salaryMonth);
        }
        sb.append(" order by salary_month ");
        String sql = super.boxUpSql(sb.toString());
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(sql, params);
    }

    @Override
    public int vwSalPersonChangeAddTotal(String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select count(*) from vw_sal_person_change_add where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salary_month = ? ");
            paramList.add(salaryMonth);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }
    @Override
    public List<Map<String, Object>> vwSalPersonChangeDelete(PaginationDTO paginationDTO, String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select * from vw_sal_person_change_delete where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salary_month = ? ");
            paramList.add(salaryMonth);
        }
        sb.append(" order by salary_month ");
        String sql = super.boxUpSql(sb.toString());
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getjdbcTemplate().queryForList(sql, params);
    }

    @Override
    public int vwSalPersonChangeDeleteTotal(String salaryMonth) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select count(*) from vw_sal_person_change_delete where 1=1 ");
        if(!StringUtils.isEmpty(salaryMonth)){
            sb.append(" and salary_month = ? ");
            paramList.add(salaryMonth);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public List<Map<String, Object>> queryPersonChangeTurnPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select 姓名,身份证号码,调出单位,调出单位编码,调入单位,调入单位编码,salary_month from vw_sal_person_change_turn where 1=1 ");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
        }
        sql.append(" order by salary_month ");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().queryForList(querySql, params);
    }


    @Override
    public int getPersonChangeTurnNum(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select count(*) from vw_sal_person_change_turn where 1=1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
        }
        return super.getjdbcTemplate().queryForObject(sql.toString(), paramList.toArray(), int.class);
    }

    @Override
    public List<Map<String, Object>> vwSalAgencyPersonTrafficAllowance(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from vw_sal_agency_person_traffic_allowance where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        sql.append(" order by agency_code");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int trafficAllowanceTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from vw_sal_agency_person_traffic_allowance where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }


    @Override
    public Map<String, Object> trafficAllowanceSum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select sum(交通补贴) 交通补贴 , sum(人数) 人数")
                .append(" from vw_sal_agency_person_traffic_allowance where 1 = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("salaryMonth"))) {
                sql.append(" and salary_month = ? ");
                paramList.add(queryParam.get("salaryMonth"));
            }
            if (!StringUtils.isEmpty(queryParam.get("bankCode"))) {
                sql.append(" and sala_card_bank = ? ");
                paramList.add(queryParam.get("bankCode"));
            }
        }
        return this.getJdbcTemplate().queryForMap(sql.toString(), paramList.toArray());
    }
}
